package com.asset.salesforce.domain;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


import com.asset.salesforce.enums.VIEWTYPE;
import com.asset.salesforce.service.CallService;
import com.asset.salesforce.service.ContactService;
import com.asset.salesforce.service.EmployeeService;
import com.asset.salesforce.service.ImportLeadUtils;
import com.asset.salesforce.service.LeadService;


@Service
@Transactional(noRollbackFor=RuntimeException.class,readOnly=true)
public class ExcelAssetLead {
	/////////////lead
	@Value("${lead.leadStatus}")
	private String leadStatus;
	
	@Value("${lead.leadOwner}")
    private String leadOwner;
	
	@Value("${lead.category}")
    private String category;
	
	@Value("${lead.companyName}")
    private String companyName;
	

	@Value("${lead.phone}")
    private String phone;
	
	@Value("${lead.street}")
    private String street;
	
	@Value("${lead.email}")
    private String email;
	
	@Value("${lead.carsNum}")
    private String carsNum;
	
	@Value("${lead.leadSource}")
    private String leadSource;

	@Value("${lead.inOutBound}")
    private String inOutBound;
	
	@Value("${lead.companyProfile}")
    private String companyProfile;
	
	//////////////////////contact

	@Value("${contact.fName}")
    private String fName;
	

	@Value("${contact.title}")
    private String title;
	
	///appointment
	@Value("${appointment.day}")
	private String appointmentDay;
	
	@Value("${appoimtment.date}")
	private String appoimtmentDate;
	
	@Value("${appointment.time}")
	private String appointmentTime;
	
	@Value("${appointment.comfirmationDateExpected}")
	private String appointmentComfirmationDateExpected;
	
	@Value("${appointment.salesMan}")
	private String appointmentSalesMan;
	
	@Value("${appointment.meetingDone}")
	private String appointmentMeetingDone;
	
	@Value("${appointment.meetingDuration}")
	private String appointmentMeetingDuration;
	
	@Value("${appointment.demoDone}")
	private String appointmentDemoDone;
	
	@Value("${appointment.offerSubmitted}")
	private String appointmentOfferSubmitted;
	
	@Value("${appointment.discountDone}")
	private String appointmentDiscountDone;
	
	@Value("${appointment.compittorOffer}")
	private String appointmentCompittorOffer;
	
	@Value("${appointment.replayPeriod}")
	private String appointmentReplayPeriod;
	
	@Value("${appointment.comments}")
	private String appointmentComments;
	
	@Value("${appointment.dealDone}")
	private String appointmentDealDone;
	
	@Value("${appointment.dealReason}")
	private String appointmentDealReason;
	
	@Value("${appointment.nextFollowUpCallDate}")
	private String appointmentNextFollowUpCallDate;
	
	@Value("${appointment.nextFollowUpCallDetails}")
	private String appointmentNextFollowUpCallDetails;
	
	@Value("${appointment.customerNeed}")
	private String appointmentCustomerNeed;
	

//	@Value("${lead.contact.phone}")
//    private String phone;
	
	/////////////////////structure
	@Value("${rowsTypes}")
	String rowTypes;
	
	@Value("${notUsedCols}")
	String notUsedCols ;
	
	@Value("${endColumns}")
	String endColumns ;
	
	@Value("${NotCallsColoum}")
	String NotCallsColoum;
	////////////calls
	@Value("${sheetCallsColums}")
	String sheetCallsColums;
	
//	@Value("${call.Start}")
//    private String startCallCol;
	
	@Value("${call.date}")
	String Date;
	
	@Value("${call.comment}")
	String Comment;
	
	@Value("${call.duration}")
	String Duration;
	
	@Value("${call.follow}")
	String Follow;
	
	@Autowired
	private EmployeeService employeeService;
	
	@Autowired
	private LeadService leadService;
	

	@Autowired
	private ContactService contactService;
	

	@Autowired
	private CallService callService;
	
	@Autowired
	private ImportLeadUtils importLeadUtils;
	
	@Transactional(noRollbackFor=RuntimeException.class,readOnly=false)
	public void  addLeadsAsset(byte []bs,Integer start,Employee userDetails) {
		try{
			InputStream inp = new ByteArrayInputStream(bs);
			HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inp));
			Sheet  sheet = workbook.getSheetAt(0);
////			Map<String,String> columnHeader=new HashMap<String, String>();
////			System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>start>>>>>>>>>>>>>>>>>>>>>>>>>>>"+start);
////			List<String> NotCallsColsList = new ArrayList<String>();
////			NotCallsColsList = getNotCallsCols();
////			
////			List<String> NotUsedColsList = new ArrayList<String>();
////			NotUsedColsList=getNotUsedCols();
////			
////			List<String> SheetCallsColumsList = new ArrayList<String>();
////			SheetCallsColumsList=getsheetCallsColums();
//			
//			List<String> callsColum = getCallsColum();
			Map<String,String> HeaderRow = new HashMap<String, String>();
			Map<String,List<String>> callsHeader = new HashMap<String, List<String>>();
			//List<>
			for (Row row : sheet) {
				if(row.getRowNum() == start -2){
					HeaderRow = getAllCollFromHeader(row);
					System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>inside start-2 "+HeaderRow.size());
					continue;
				}
				else if(row.getRowNum() == start -3){
					callsHeader = getCallsColumn(row);
					
					
					System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>inside start-3 "+callsHeader.size());
					continue;
				}else if(row.getRowNum()>=(start-1)){
					Employee employee = employeeService.getEmployeeByEmail(leadOwner);
					 Lead lead = new Lead();
					 Contact contact  = new Contact();
					 Appointment appointment = new Appointment();
					 Employee appointmentSales = employeeService.getEmployeeByEmail(appointmentSalesMan);
						if(employee != null){
							appointment.setSalesMan(appointmentSales);
							
						}
						
					 List<Call> calls = setCallObject(row, callsHeader, HeaderRow);
					 System.out.println(">>>>>>>>>>>>>>>>>>callsSize>>>>>>>>>>>>.."+calls.size());
					  Integer checkNullsRowsFlag=new Integer(0);
					  for (Cell cell : row) {
						  String columnName =  CellReference.convertNumToColString(cell.getColumnIndex());
						  lead.setLeadOwner(employee);
						   if(category.equalsIgnoreCase(columnName)){
								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setCategory(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}else if(leadSource.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setLeadSource(cell.toString());
								}
								continue;
							}else if(carsNum.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									if(lead.getCarsNum() == null){
										lead.setCarsNum(cell.toString());
									}
								}
								continue;
							}
							else if(email.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setEmail(cell.toString());
									contact.setEmail(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(street.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setStreet(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(phone.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									switch (cell.getCellType()) {
									case Cell.CELL_TYPE_NUMERIC:
										System.out.println("l;sjabfgakvbsfsaiggfyvhmsafhkahsvf"+cell.getNumericCellValue());
										contact.setPhone(new Double(cell.getNumericCellValue()).toString());
										contact.setMobile(new Double(cell.getNumericCellValue()).toString());
										lead.setPhone(new Double(cell.getNumericCellValue()).toString());
										lead.setMobile(new Double(cell.getNumericCellValue()).toString());
										break;

									default:
										contact.setPhone(cell.toString());
										contact.setMobile(cell.toString());
										lead.setMobile(cell.toString());
										lead.setPhone(cell.toString());
										break;
									}
									
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(companyName.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setCompanyName(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(leadStatus.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setLeadStatus(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(inOutBound.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setInOutBound(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}else if(companyProfile.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setDescription(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}else if(title.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setTitle(cell.toString());
									contact.setTitle(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(fName.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									lead.setfName(cell.toString());
									contact.setfName(cell.toString());
									lead.setlName("");
									contact.setlName("");
									checkNullsRowsFlag++;
								}
								continue;
							}
						   
							else if(appointmentDiscountDone.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setDiscountDone(importLeadUtils.checkCompititorOffer(cell.toString()));
									checkNullsRowsFlag++;
								}else if(cell.toString() != null){
									appointment.setDiscountDone(importLeadUtils.checkCompititorOffer(cell.toString()));
								}
								continue;
							}
							else if(appointmentOfferSubmitted.equalsIgnoreCase(columnName)){
								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setOfferSubmitted(importLeadUtils.checkCompititorOffer(cell.toString()));
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(appointmentReplayPeriod.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setReplyPeriod(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(appointmentComments.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setComment(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(appointmentDealDone.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setDealClosingDone(importLeadUtils.checkCompititorOffer(cell.toString()));
									checkNullsRowsFlag++;
								}else if(cell.toString() != null){
									appointment.setDealClosingDone(importLeadUtils.checkCompititorOffer(cell.toString()));
								}
								continue;
							}

							else if(appointmentDealReason.equalsIgnoreCase(columnName)){
								

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setDealClosingReason(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(appointmentNextFollowUpCallDate.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setNextFollowUp(true);
									
									if(importLeadUtils.checkDateInFormDayMonthYear(cell.toString())){
										Date date = importLeadUtils.dateInFormDayMonthYear(cell.toString());
//										call.setDate(date);
										appointment.setFollowUpDate(date);
										checkNullsRowsFlag++;
										
									}
									else if(importLeadUtils.checkDateInFormDayMonthYearDashes(cell.toString())){
										Date date = importLeadUtils.dateInFormDayMonthYearDashes(cell.toString());
//										call.setDate(date);
										appointment.setFollowUpDate(date);
										checkNullsRowsFlag++;
									}
									
									checkNullsRowsFlag++;
								}
								continue;
							}

							else if(appointmentNextFollowUpCallDetails.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setFollowUpDetails(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(appointmentDemoDone.equalsIgnoreCase(columnName)){
								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setDemoDone(importLeadUtils.checkCompititorOffer(cell.toString()));
									checkNullsRowsFlag++;
								}else if(cell.toString() != null){
									appointment.setDemoDone(importLeadUtils.checkCompititorOffer(cell.toString()));
								}
								continue;
							}
							else if(appointmentMeetingDuration.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setMeetingDuration(cell.toString());
									checkNullsRowsFlag++;
								}else if(cell.toString() != null){
									appointment.setMeetingDuration(cell.toString());
								}
								continue;
							}
							else if(appointmentMeetingDone.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setMeetingDone(importLeadUtils.checkCompititorOffer(cell.toString()));
									checkNullsRowsFlag++;
								}
								continue;
							}
//							else if(appointmentSalesMan.equalsIgnoreCase(columnName)){
//
//								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
//									
//								}
//								continue;
//							}
							else if(appointmentComfirmationDateExpected.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setConfirmationDateExpected(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(appointmentCustomerNeed.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setCustomerNeed(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							else if(appointmentTime.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setTime(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
							
							else if(appoimtmentDate.equalsIgnoreCase(columnName)){
								
								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									System.out.println(appoimtmentDate+" @@@@@@@@@@@@@@@@@@@@@@@Big iffffffff"+cell.toString());
									if(importLeadUtils.checkDateInFormDayMonthYear(cell.toString())){
										Date date = importLeadUtils.dateInFormDayMonthYear(cell.toString());
										System.out.println(appoimtmentDate+" @@@@@@@@@@@@@@@@@@@@@@@ iffffffff"+cell.toString());
										appointment.setDate(date);
										checkNullsRowsFlag++;
									}
									else if(importLeadUtils.checkDateInFormDayMonthYearDashes(cell.toString())){
										System.out.println(appoimtmentDate+" @@@@@@@@@@@@@@@@@@@@@@@ else if"+cell.toString());
										Date date = importLeadUtils.dateInFormDayMonthYearDashes(cell.toString());
										appointment.setDate(date);
										checkNullsRowsFlag++;
									}
									else if(cell.getCellType()== Cell.CELL_TYPE_NUMERIC){
										System.out.println(appoimtmentDate+" @@@@@@@@@@@@@@@@@@@@@@@ else if22"+cell.toString());
										if(DateUtil.isCellDateFormatted(cell)){
											System.out.println(appoimtmentDate+" @@@@@@@@@@@@@@@@@@@@@@@ else if22if"+cell.toString());
											appointment.setDate(cell.getDateCellValue());
										}
									}
								}
								continue;
							}
							else if(appointmentDay.equalsIgnoreCase(columnName)){

								if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
									appointment.setDay(cell.toString());
									checkNullsRowsFlag++;
								}
								continue;
							}
						   
						   if(checkNullsRowsFlag >0){
							   
							   lead.setIsDeleted(false);
							   lead.setIsConverted(false);
							   leadService.addLead(lead);
							   contact.setViewType(VIEWTYPE.Lead.getName());
							   contact.setCreator(employee);
							   contact.setLeadId(lead);
							   contact.setParentId(lead.getId());
							   contact.setParentName(lead.getfName());
							   contact.setIsDeleted(false);
							   contactService.addContact(contact);
							   if(calls.size()>0){
								  for(Call call : calls){
									if(call != null){
										call.setParentId(lead.getId());
										call.setParentType("lead");
										call.setCrmStaff(employee);
										call.setContactedPerson(contact);
										callService.addCall(call);
										lead.setDate(calls.get(0).getDate());
									   lead.setDateCreated(calls.get(0).getDate());
									   lead.setDateModified(calls.get(0).getDate());
									   contact.setDate(calls.get(0).getDate());
									   contact.setDateCreated(calls.get(0).getDate());
									   contact.setDateModified(calls.get(0).getDate());
									   leadService.addLead(lead);
									   contactService.addContact(contact);
									}
								    
								  }
								   
   
							   }
						   }
					  }
					
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	Map<String,List<String>>getCallsColumn(Row row){
		int counter = 0;
		int count = 0;
		Map<String, List<String>>map = new HashMap<String,List<String>>();
		for(Cell cell : row){
			//System.out.println("inside for contain call condition>>>>>>>>>>>>>>>>>>>>>"+cell.toString()+"lkdfjksd;gs"+CellReference.convertNumToColString(cell.getColumnIndex())+"ol,p;l,ol"+cell.getRowIndex());
			String cellString =cell.toString();
			
			if(cellString.contains("Call")){
				 counter = 0;
				
				System.out.println(">>>>>>>>>>>>>>>>>>>>>inside if contain call condition>>>>>>>>>>>>>>>>>>>>>"+cell.isPartOfArrayFormulaGroup());
				
				List<String> list= new ArrayList<String>();
				
				String collum = CellReference.convertNumToColString(cell.getColumnIndex());
				list.add(collum);
				//sSystem.out.println("<<<<<<<<<<<<<<<<<<<<<<<<<<<COUNTER>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"+row.getCell(cell.getColumnIndex()+1).toString().isEmpty());
				counter++;
				System.out.println("erroooooooooooooooooooooooooooooooooor>>>> CellReference.convertNumToColString" + CellReference.convertNumToColString(cell.getColumnIndex()+counter) + "row.getCell(cell.getColumnIndex()+counter).toString()" + row.getCell(cell.getColumnIndex()+counter).toString());
				while(checkIfInUsedColumns(CellReference.convertNumToColString(cell.getColumnIndex()+counter))&&row.getCell(cell.getColumnIndex()+counter).toString().isEmpty()){
					System.out.println("<<<<<<<<<<<<<<<<<<<<<<<<<<<COUNTER>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"+row.getCell(cell.getColumnIndex()+1).toString().isEmpty());
					
					list.add(CellReference.convertNumToColString(cell.getColumnIndex()+counter));
					
					counter++;
					
					System.out.println("inside for contain call condition>>>>>>>>>>>>>>>>>>>>>"+cell.toString()+" lkdfjksd;gs "+CellReference.convertNumToColString(cell.getColumnIndex())+" ol,p;l,ol "+cell.getRowIndex());
				}
				
					map.put("call"+count, list);
					for(String s:list){
						System.out.println("klli,kimsjshsdsksdndsbbbb"+s);
					}
					
					count++;
				
				//while(CellReference.convertNumToColString(cell.getColumnIndex()+1))
			}
		}
		return map;
	}
	public List<Call> setCallObject(Row Currentrow, Map<String,List<String>> callsHeader,Map<String,String> HeaderRow) {
		Set<String> set = callsHeader.keySet();
     		for(Cell cell :Currentrow){
			 CellReference.convertNumToColString(cell.getColumnIndex());
			 
		}
//     		List<String> list =  getCallsColum();
//     		for(list)
		List<Call> calls = new ArrayList<Call>();
		//Map<String, List<String>>  map = getCallsColumn(callsRow);
		Iterator<Entry<String, List<String>>> iterator = callsHeader.entrySet().iterator();
		while(iterator.hasNext()){
			Call call = new Call();
			int callFlag = 0;
			Map.Entry<String,List<String>> pairs =(Map.Entry<String,List<String>>)iterator.next();
			List<String> list = pairs.getValue();
			
			for(String string:list){
				
				String field= HeaderRow.get(string);
				if(field.contains(Date)){
					Cell cell = Currentrow.getCell(CellReference.convertColStringToIndex(string));
					if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
						Date  date =  getCallDate(cell.toString());
						call.setDate(date);
						call.setDateCreated(date);
						call.setDateCreated(date);
						callFlag++;
					}
					
					
				}else if(field.contains(Comment)){
					Cell cell = Currentrow.getCell(CellReference.convertColStringToIndex(string));
					if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
						call.setFollowUpDetails(cell.toString());
						
					}
					
					
				}else if(field.contains(Duration)){
					Cell cell = Currentrow.getCell(CellReference.convertColStringToIndex(string));
					if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
						
						call.setComment(cell.toString());
					}
					
					
				}else if(field.contains(Follow)){
					Cell cell = Currentrow.getCell(CellReference.convertColStringToIndex(string));
					Date  date =  getCallDate(cell.toString());
					if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
						call.setFollowUpDate(date);
						callFlag++;
					}
					
				}
			}
			if(callFlag > 0){
				calls.add(call);
			}
		}
		return calls;
	}
//	public Call setCallField(String columName,Map<String,String> HeaderRow,Call call,Cell cell) {
////		List<String> NotCallsColsList = new ArrayList<String>();
////		NotCallsColsList = getNotCallsCols();
////		
////		List<String> NotUsedColsList = new ArrayList<String>();
////		NotUsedColsList=getNotUsedCols();
////		
////		List<String> SheetCallsColumsList = new ArrayList<String>();
////		SheetCallsColumsList=getsheetCallsColums();
//		
//		Integer callFlag = new Integer(0);
//		List<String> callsColum = getCallsColum();
//		if(callsColum.contains(columName)){
//			 //Map<String,String> map=getAllCallsFromHeader(row);
//			//System.out.println("><LOKOK"+columName);
//			Set<String>list = HeaderRow.keySet();
//			System.out.println("<<<<<<<<<<<<<<<<<<<<list.size()>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>."+list.size());
//			String field = HeaderRow.get(columName);
//			if(field.contains(Date)){
//				
//				if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
//					Date  date =  getCallDate(cell.toString());
//					call.setDate(date);
//					callFlag++;
//				}
//				
//				
//			}else if(field.contains(Comment)){
//				if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
//					call.setComment(cell.toString());
//					callFlag++;
//				}
//				
//				
//			}else if(field.contains(Duration)){
//				if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
//					call.setComment(cell.toString());
//					callFlag++;
//				}
//				
//				
//			}else if(field.contains(Follow)){
//				Date  date =  getCallDate(field);
//				if(cell.toString() != null && !cell.toString().equalsIgnoreCase("")){
//					call.setDate(date);
//					call.setDateCreated(date);
//					call.setDateCreated(date);
//					callFlag++;
//				}
//				
//			}
//		}
//			return call;
//		
//	}
	public Date getCallDate(String date) {
		if(date != null && !date.equalsIgnoreCase("")){
		String[] fields =date.split(",");
		DateFormat dateFormat= new SimpleDateFormat("dd/MM/yyyy");
		if(fields[0].equalsIgnoreCase("march")){
			String dateString=fields[1]+"/03/"+fields[2];
			try {
				return dateFormat.parse(dateString);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else if(fields[0].equalsIgnoreCase("february")){
			String dateString=fields[1]+"/02/"+fields[2];
			try {
				return dateFormat.parse(dateString);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else if(fields[0].equalsIgnoreCase("april")){
			String dateString=fields[1]+"/04/"+fields[2];
			try {
				return dateFormat.parse(dateString);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else if(fields[0].equalsIgnoreCase("january")){
			String dateString=fields[1]+"/01/"+fields[2];
			try {
				return dateFormat.parse(dateString);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else{
			return null;
		}
	}
		return null;
		
	}
	public Boolean isCall(String coloum) {
		return null;
	}
	public String  getCallsNumber(Row row){
		return null;
	}
//	public Map<String,String>getAllCallsColumn(Map row){
//		Map<String,String> map =new HashMap<String,String>();
//
//		return null;
//	}
	
	public List<String> getCallsColum(){
		List<String> list = new ArrayList<String>();
		List<String> notCallsColums =getNotCallsCols();
		List<String> sheetCallsColums = getsheetCallsColums();
		for(String string : sheetCallsColums){
			for(String string2:notCallsColums){
				if(!string2.equalsIgnoreCase(string))
					list.add(string);
			}
		}
		return list;
	}
	public List<String> getNotUsedCols() {
		List<String> list = new ArrayList<String>();
		String[] strings = notUsedCols.split(",");
		for(String string : strings){
			list.add(string);
		}
		return list;
	}
	public List<String> getNotCallsCols() {
		List<String> list = new ArrayList<String>();
		String[] strings = NotCallsColoum.split(",");
		for(String string : strings){
			list.add(string);
		}
		return list;
	}
	public List<String> getsheetCallsColums() {
		List<String> list = new ArrayList<String>();
		String[] strings = sheetCallsColums.split(",");
		for(String string : strings){
			list.add(string);
		}
		return list;
	}
	public Boolean checkIfInUsedColumns(String colIndex) {
		String [] strings = notUsedCols.split(",");
		for(String string : strings){
			if(string.equalsIgnoreCase(colIndex)){
				return false;
			}
		}
		return true;
	}
	public Boolean checkIfInCallsColumns(String colChar) {
		String [] strings = NotCallsColoum.split(",");
		for(String string : strings){
			if(string.equalsIgnoreCase(colChar)){
				return false;
			}
		}
		return true;
	}
	public Map<String, String> getAllCollFromHeader(Row row) {
		Map<String,String> map=new HashMap<String, String>();
		for(Cell cell : row){
			//CellReference cellReference = new CellReference(row.getRowNum(), cell.getColumnIndex());
			//System.out.println(CellReference.convertNumToColString(cell.getColumnIndex())+"<><><><><><><><><><<><><><><><><><><<>======="+cell.toString());
			if(checkIfInUsedColumns(CellReference.convertNumToColString(cell.getColumnIndex()))){
				map.put(CellReference.convertNumToColString(cell.getColumnIndex()), cell.toString());
			}
			
			
		}
		return map;
	}
}
